/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ig.util;

import java.io.FileInputStream;
import java.math.RoundingMode;
import java.util.Hashtable;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 *
 * @author jsanchez
 */
public class GenVectorMatricesCir051 {

    public static int[] getRowCol(String value, HSSFSheet sheet) {
        int[] xy = new int[2];
        for (int i = 0; i < 50; i++) {
            for (int j = 0; j < 50; j++) {
                HSSFRow row = sheet.getRow(i);
                HSSFCell cell = null;
                try {
                    cell = row.getCell((short) j);
                } catch (Exception e) {
                }

                if (cell != null) {
                    if (cell.getCellType() == 1 && cell.getStringCellValue().toUpperCase().trim().equals(value.toUpperCase().trim())) {
                        xy[0] = i;
                        xy[1] = j;
                        i = j = 51;
                    }
                }
            }
        }
        return xy;
    }

    private static int[] getRowCol(String value, HSSFSheet sheet, int[] xy) {
        int[] _xy = new int[2];
        for (int i = xy[0] + 1; i < 100; i++) {
            for (int j = xy[1]; j < 100; j++) {
                HSSFRow row = sheet.getRow(i);
                HSSFCell cell = null;
                try {
                    cell = row.getCell((short) j);
                } catch (Exception e) {
                }

                if (cell != null) {
                    if (cell.getCellType() == 1 && cell.getStringCellValue().toUpperCase().trim().equals(value.toUpperCase().trim())) {
                        //System.out.println(cell.getStringCellValue());
                        _xy[0] = i;
                        _xy[1] = j;
                        i = j = 100;
                    }
                }
            }
        }
        return _xy;
    }

    private static int getNumberFactotores(int[] xy, HSSFSheet sheet) {
        int _xy = 0;
        for (int i = xy[0]; i < 100; i++) {
            HSSFRow row = sheet.getRow(i);
            HSSFCell cell = null;
            try {
                cell = row.getCell((short) xy[1]);
            } catch (Exception e) {
            }
            if (cell != null) {
                if (cell.getCellType() == 0) {
                    cell.getNumericCellValue();
                    _xy++;
                }
            } else {
                break;
            }
        }
        return _xy;
    }

    private static int getDecimales(HSSFSheet sheet) {
        int decimales;
        try {
            HSSFRow row = sheet.getRow(0);
            HSSFCell cell = null;
            cell = row.getCell((short) 1);
            decimales = Double.valueOf(cell.getNumericCellValue()).intValue();
        } catch (Exception e) {
            decimales = 8;
        }
        return decimales;
    }

    private static Double[][] getVolatilidades(int nf, int[] xy, HSSFSheet sheet) {
        int decimales = GenVectorMatricesCir051.getDecimales(sheet);

        Double[][] vol = new Double[nf][1];
        int ii = 0;
        for (int i = xy[0]; i < nf + xy[0] + 1; i++) {
            HSSFRow row = sheet.getRow(i);
            HSSFCell cell = null;
            try {
                cell = row.getCell((short) (xy[1] + 1));
            } catch (Exception e) {
            }
            if (cell != null) {
                if (cell.getCellType() == 0) {
                    vol[ii++][0] = GenVectorMatricesCir051.aplicarFormato(cell.getNumericCellValue(), decimales);
                }
            } else {
                break;
            }
        }
        return vol;
    }

    private static Double[][] getMatrizCorrelacion(int nf, int[] xy, HSSFSheet sheet) {
        int decimales = GenVectorMatricesCir051.getDecimales(sheet);
        Double[][] vol = new Double[nf][nf];
        int ii = 0;
        int jj = 0;
        for (int i = xy[0] + 2; i < nf + xy[0] + 2; i++) {
            for (int j = xy[1] + 1; j < nf + xy[1] + 1; j++) {
                HSSFRow row = sheet.getRow(i);
                HSSFCell cell = null;
                try {
                    cell = row.getCell((short) j);
                } catch (Exception e) {
                }

                if (cell != null) {
                    if (cell.getCellType() == 0) {
//                        System.out.print(" " + cell.getNumericCellValue());
                        vol[ii][jj++] = GenVectorMatricesCir051.aplicarFormato(cell.getNumericCellValue(), decimales);
                    }
                }
            }
//            System.out.println();
            ii++;
            jj = 0;
        }
        return vol;
    }

    private static Double[][] getMatriz(int row_, int col_, int[] xy, HSSFSheet sheet) {
        int decimales = GenVectorMatricesCir051.getDecimales(sheet);
        Double[][] vol = new Double[row_][col_];
        int ii = 0;
        int jj = 0;
        for (int i = xy[0] + 1; i < row_ + xy[0] + 1; i++) {
            for (int j = xy[1] + 1; j < col_ + xy[1] + 1; j++) {
                HSSFRow row = sheet.getRow(i);
                HSSFCell cell = null;
                try {
                    cell = row.getCell((short) j);
                } catch (Exception e) {
                }

                if (cell != null) {
                    if (cell.getCellType() == 0) {
//                        System.out.print(" " + cell.getNumericCellValue());
                        vol[ii][jj++] = GenVectorMatricesCir051.aplicarFormato(cell.getNumericCellValue(), decimales);
                    }
                }
            }
//            System.out.println();
            ii++;
            jj = 0;
        }
        return vol;
    }

    public static void main(String[] args) throws Exception {
        GenVectorMatricesCir051.loadMatricez("/home/Entrada/CIRCULAR 051/matricesreporjul2008.xls", 6);
    }

    /**
     * Carga el xls de la matrices de la circular 051 y retorna un Hashtable con los 
     * key a cotinuacion:
     * 
     * <BR>VOLATILIDADES -->Double[] 
     * <BR>MATRIZ        -->Double[][]
     * <BR>MATRIZ_CHO_COP-->Double[][]
     * <BR>MATRIZ_CHO_UVR-->Double[][]
     * <BR>MATRIZ_CHO_ME -->Double[][]
     * 
     * @param xls :la ruta el xls a cargar
     * @return El Hashtable con los valores cargados
     * @throws java.lang.Exception
     */
    public static Hashtable<String, Object> loadMatricez(String xls, int opcion) throws Exception {
        Hashtable<String, Object> h = new Hashtable<String, Object>();
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(xls));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);//Matriz Correlaciones ordenada

        if (opcion == 4 || opcion == 6 || opcion == 12 || opcion==14) {
            sheet = wb.getSheetAt(1);//Matriz Volatilidades estresadas
            int[] xy = GenVectorMatricesCir051.getRowCol("Fila", sheet);
            int nf = GenVectorMatricesCir051.getNumberFactotores(xy, sheet);
            Double[][] Volatilidades = GenVectorMatricesCir051.getVolatilidades(nf, xy, sheet);
            //System.out.println(Volatilidades[0]);
            h.put("VOLATILIDADES", Volatilidades);
        }

        if (opcion == 2 || opcion == 6 || opcion == 10 || opcion==14) {
            sheet = wb.getSheetAt(0);//Matriz de correlacion            
            int[] xy = GenVectorMatricesCir051.getRowCol("Fila", sheet);
            int nf = GenVectorMatricesCir051.getNumberFactotores(xy, sheet);
            Double[][] Matriz = GenVectorMatricesCir051.getMatrizCorrelacion(nf, xy, sheet);
            h.put("MATRIZ", Matriz);
        }
        if (opcion == 8 || opcion == 10 || opcion == 12 || opcion==14) {
            sheet = wb.getSheetAt(2);//Matriz de choques
            int[] xy = GenVectorMatricesCir051.getRowCol("Fila", sheet);
            //System.out.println(xy[0] + "," + xy[1]);
            Double[][] Matriz = GenVectorMatricesCir051.getMatriz(3, 9, xy, sheet);
            h.put("MATRIZ_CHO_COP", Matriz);

            xy = GenVectorMatricesCir051.getRowCol("Fila", sheet, xy);
            //System.out.println(xy[0] + "," + xy[1]);
            Matriz = GenVectorMatricesCir051.getMatriz(3, 9, xy, sheet);
            h.put("MATRIZ_CHO_UVR", Matriz);

            xy = GenVectorMatricesCir051.getRowCol("Fila", sheet, xy);
            //System.out.println(xy[0] + "," + xy[1]);
            Matriz = GenVectorMatricesCir051.getMatriz(3, 9, xy, sheet);
            h.put("MATRIZ_CHO_ME", Matriz);
        }
        return h;
    }

    private static Double aplicarFormato(Double v, int decimales) {
        String FOR = "#.";
        for (int i = 1; i <= decimales; i++) {
            FOR += "0";
        }
        java.text.DecimalFormat df2 = new java.text.DecimalFormat(FOR);
        df2.setRoundingMode(RoundingMode.HALF_UP);
        return Double.valueOf(df2.format(v).replace(",", "."));
    }
}